import datetime
import uuid

from sqlalchemy import create_engine, Column, Integer, String, TIMESTAMP, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# engine = create_engine("mysql+pymysql://root:xl123456@localhost/test",
#                        # echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
#                        echo=True,
#                        # pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
#                        pool_size=8,
#                        # pool_recycle: 设置时间以限制数据库多久没连接自动断开
#                        pool_recycle=60 * 30
#                        )
engine = create_engine("sqlite:///./api.db", echo=False, pool_size=8, pool_recycle=60 * 30)
Base = declarative_base()


class Record(Base):
    __tablename__ = 'record'
    id = Column(Text, primary_key=True, default=str(uuid.uuid4()))
    case_name = Column(Text, default="")
    case_data = Column(Text, default="{'cases': []}")
    create_time = Column(TIMESTAMP, default=datetime.datetime.utcnow(), index=True)
    update_time = Column(TIMESTAMP, default=datetime.datetime.utcnow(), onupdate=datetime.datetime.utcnow(),
                         index=True)


class RecordLog(Base):
    __tablename__ = 'record_log'

    id = Column(Text, primary_key=True, default=str(uuid.uuid4()))
    record_id = Column(Text, index=True)
    record_case_name = Column(Text)
    record_case_data = Column(Text)
    run_case_data = Column(Text)
    create_time = Column(TIMESTAMP, default=datetime.datetime.now())


Base.metadata.create_all(engine)
DbSession = sessionmaker(bind=engine)

session = DbSession()

"""

session = DbSession()

a = Record(case_name="1234", case_data="{'cases': []}")
session.add(a)
session.commit()


# session的常见操作方法包括：
#
# flush：预提交，提交到数据库文件，还未写入数据库文件中
# commit：提交了一个事务
# rollback：回滚
# close：关闭


# .first()
# .all()
# filter_by()   只适合等值查询，其参数为字典传参的方式
# filter()

# 基础查询汇总,直接打印一个类的时候，具体打印什么内容，由类的__repr__魔术方法决定，可以重写
# users = session.query(Demo).all()
# SELECT * FROM demo;

# users = session.query(Demo.name).all()
# select demo.name from demo;

# users = session.query(Demo).filter_by(age=15, money=5.1508).all()
# select * from demo where age=15 and money=5.1508;

# users = session.query(Demo).filter(or_(Demo.age > 65, Demo.money >= 7)).all()
# select * from demo where age>65 or money>=7;

# users = session.query(Demo).limit(3).all()
# select * from demo limit(3);

# users = session.query(Demo).limit(5).offset(3).all()
# select * from demo limit(3,5)

# count = session.query(Demo).filter(Demo.money > 6).count()
# select count(1) from demo where money>6;
# print(count)

# users=session.query(Demo).distinct().count()
# SELECT count(*) AS count_1 FROM (SELECT DISTINCT * FROM demo) AS anon_1;
# print(users)

# users = session.query(Demo).order_by(Demo.age.desc()).all()
# select * from demo order by age desc;

# users=session.query(Demo).filter(Demo.name.like("%李%")).all()
# select * from demo where name like '%李%';

# users = session.query(Demo.age).group_by(Demo.age).all()
# select demo.age from demo group by age;
# print(users)

# users = session.query(Demo.age).group_by(Demo.age).having(Demo.age>60).all()
# select demo.age from demo group by demo.age having demo.age>60;
# print(users)

# users = session.query(func.sum(Demo.age)).filter(Demo.age > 65).first()
# select sum(demo.age) from demo where demo.age>65 limit 1;
# print(users)


# 多表查询
# SELECT * FROM demo INNER JOIN demo1 ON demo.name = demo1.name1 WHERE demo.age > 60
# result = session.query(Demo, Demo1).join(Demo1, Demo.name == Demo1.name1).filter(Demo.age > 60).all()
# for demo, Demo1 in result:
#     print(demo.name)


# SELECT sum(demo.age), sum(demo1.age1) FROM demo LEFT OUTER JOIN demo1 ON demo.name = demo1.name1 WHERE demo.age > 60
# result = session.query(func.sum(Demo.age), func.sum(Demo1.age1)).outerjoin(Demo1, Demo.name == Demo1.name1).filter(
#     Demo.age > 60).all()
# for demo, Demo1 in result:
#     print(demo, Demo1)

# 复杂查询 and 和 or 混用

# select * from user where username like '%李%' or (userid>12 and nickname='谢谢'）;
# session.query(user).filter(or_(username.like("%李%"),and_(userid>12,nickname=="谢谢"))).all()

# 三表查询


# 用sqlalchemy执行原声SQL
# result=session.execute("select * from demo where id=2").fetchall()
# result=session.query(Demo).filter_by(id=12).all()
# print(result)
# print(result[0].create_time)

# 删除
# session.execute("delete from demo where id=1")
# session.commit()
#
#

"""

